const xlsx = require('node-xlsx');
const fs = require('fs');

const tree = {};
const workSheetsFromFile = xlsx.parse(`/Users/lizhi/Downloads/jjc_pp_account.xlsx`);
const data = workSheetsFromFile[0].data

const uvcV4Mysql = require('knex')({
    client: 'mysql',
    connection: {
        host: '139.9.141.62',
        user: 'root',
        password: 'UvcM123*!',
        database: 'uvc_pp',
        port: 3306,
    }
});

const uvcV5Mysql = require('knex')({
    client: 'mysql',
    connection: {
        host: '119.3.249.105',
        user: 'root',
        password: 'UvcM123*!',
        database: 'daysp',
        port: 3306,
    }
});

// [
//     '628d08a7382b9afd2a9aa80e',
//     '0',
//     '[ "_575891d218c5f2c91300@dve" ]',
//     '1',
//     '0',
//     44705.68934817129,
//     '52:4e:40:00:0e:e3',
//     44705.68947780092,
//     '[ { "rightStatus" : { "$numberInt" : "1" }, "rightOpenTime" : "2021-04-04 22:57:23", "mac" : "52:4e:40:00:0e:e3", "rightType" : "SVIP日包", "username" : "_575891d218c5f2c91300@dve" }, { "rightStatus" : { "$numberInt" : "1" }, "rightOpenTime" : "2020-01-07 10:40:39", "mac" : "52:4e:40:00:0e:e3", "rightType" : "会员权益", "username" : "_575891d218c5f2c91300@dve" } ]'
//   ]


(async () => {
    console.log('data', data.length)

    for (let i = 1; i < data.length; i++) {
        const item = data[i];
        const accountList = JSON.parse(item[2])
        const account = accountList.shift();
        const otherAccount = accountList.join(',')
        const accountNum = item[3]
        const appType = item[4]
        const mac = item[6]
        const vipInfo = item[8]

        if (tree[mac]) continue

        if (accountNum > 1) {
            const newItem = {
                mac,
                account,
                otherAccount,
                accountNum,
                appType: appType == 0 ? '年会员' : '天会员'
            }

            const device = await getDevice(mac, appType)

            if (device) {
                newItem.hotelName = device.hotelName
                newItem.deviceName = device.name
            }

            console.log('newItem', newItem)

            tree[mac] = newItem
        }

    }

    const exportData = []
    for (const key in tree) {
        const item = tree[key]
        exportData.push([item.mac, item.account, item.otherAccount, item.accountNum, item.hotelName, item.deviceName, item.appType])
    }


    exportXlsx([
        ['mac', '当前账号', '历史账号', '账号数量', '酒店名称', '房间编号', '会员类型'],
        ...exportData
    ])
})()


function exportXlsx(data) {
    const buffer = xlsx.build([
        {
            name: 'sheetName',
            data,
        },
    ])

    fs.writeFile('PP账号清洗.xlsx', buffer, function (err) {
        if (err) console.log('err', err)
    });
}


async function getDevice(mac, appType) {
    const { ctx } = this;
    const knex = appType == 0 ? uvcV4Mysql : uvcV5Mysql
    const data = await knex('hotel_room').where({ mac }).limit(1)
    const device = data[0]

    return device
}